using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ExtendBLL
{
    public class LabInfo:LabMS.BLL.Lab
    {
        public DataSet GetLabInfo(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.*,b.[Name] as Lab_CategoryName,c.[Name] as Lab_LevelName,d.[Name] as Lab_StateName,e.[Name] as Lab_TypeName from Lab a left join Dictionary b on a.Lab_Category=b.Code left join Dictionary c on a.Lab_Level=c.Code left join Dictionary d on a.Lab_State=d.Code left join Dictionary e on a.Lab_Type=e.Code ");
            if (strWhere != "")
            {
                strSql.Append(" where " + strWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetLabInfoAndRoomNumAndArea(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select * from (select Isnull(b.[ID],a.[ID]) as SubID, a.[ID],a.Lab_Name,b.Lab_Name as SubLab_Name from Lab a full join Lab b on a.[ID] = b.Lab_ParentID" +
                        " where a.Lab_ParentID is null and a.[ID] is not null) as c left join (select LabID, count(*) as Room_Num,sum(cast (Location_Area as float)) as Room_Area from Location group by LabID) as d on c.SubID=d.LabID) as e " +
                        "left join (select e1.[ID],sum(e1.Room_Area) as AllRoom_Area from (select * from (select Isnull(b1.[ID],a1.[ID]) as SubID, Isnull( a1.[ID],b1.[ID]) as [ID] from Lab a1 full join Lab b1 on a1.[ID] = b1.Lab_ParentID " +
                        "where a1.Lab_ParentID is null) as c1 left join (select LabID,sum(cast (Location_Area as float)) as Room_Area from Location group by LabID) as d1 on c1.SubID=d1.LabID) as e1 group by e1.[ID] ) as f on e.[ID]=f.[ID] where 1=1");
            if (ID != 0)
            {
                strSql.Append(" and e.[ID]=" + ID);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetSubInfoByID(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.[ID],a.Lab_Name,a.Lab_Investment,a.[Lab_SetUpTime],a.Lab_InCharge,b.* from Lab A left join (select LabID, count(*) as Room_Num,sum(cast (Location_Area as float)) as Room_Area from Location group by LabID) as b on a.[ID]=b.[LabID] where a.Lab_ParentID=" + ID);
            
            return DbHelperSQL.Query(strSql.ToString());
            
        }

        public int DeleteLab(List<string> strID, List<string> strTable, List<string> strColName)
        {
            #region PareCommand
            List<CommandInfo> lsComInfo = new List<CommandInfo>();
            StringBuilder strSql1 = new StringBuilder();
            for (int i = strTable.Count - 1; i >= 0; i--)
            {
                if (i<2)
                {
                    strSql1.Append(" delete from " + strTable[i] + " where [" + strColName[i] + "] = " + strID[i] + "");
                }
                else
                {
                    strSql1.Append(" update " + strTable[i] + " Set [" + strColName[i] + "] = null  where [" + strColName[i] + "]=" + strID[i]);
                }
            }
            
            SqlParameter[] parameter ={  };
            lsComInfo.Add(new CommandInfo(strSql1.ToString(), parameter));

            DbHelperSQL.ExecuteSqlTranWithIndentity(lsComInfo);


            return 1;
            #endregion 
        }
    }
}
